import pandas, duckdb
from utils import create_windows
from time import strftime
import os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection



# timenow = str(strftime("%Y%m%d%H%M%S"))
def run(dwbh, dwid, zbid, zb, startday, endday, filepath, timenow, mode):
  excel_data = pandas.read_excel(filepath, sheet_name=None, dtype=str)
  df1 = excel_data.get('凭证头')
  df2 = excel_data.get('凭证分录')
  df3 = excel_data.get('凭证辅助')
  conn = get_connection()
  conn.register('temp_pz_main', df1)
  conn.register('temp_pz_fl', df2)
  conn.register('temp_pz_fzys', df3)


  #删除已有数据
  if mode == 'sd':
    startday, endday = conn.execute(f"select MIN(凭证日期), MAX(凭证日期) from temp_pz_main where 核算账簿编号 = '{zb}'").fetchall()[0]
    conn.execute(f"""
                delete from T_API_PZ where accdocdate between '{startday}' and '{endday}' and LEDGERBH = '{zb}';
                delete from T_API_PZFL where ACCDOCID not in (select id from T_API_PZ);
                delete from T_API_FZYS where ACCDOCID not in (select id from T_API_PZ);
               """)
  else:
    conn.execute(f"""
                delete from T_API_PZ where accdocdate between '{startday}' and '{endday}' and LEDGERBH = '{zb}';
                delete from T_API_PZFL where ACCDOCID not in (select id from T_API_PZ);
                delete from T_API_FZYS where ACCDOCID not in (select id from T_API_PZ);
    """)
    
  # conn.execute("""
  #       create or replace table t_yl_pz as
  #              select
  #               凭证ID as id,
  #               '"""+dwbh+"""' as 单位编号,
  #               核算账簿编号 as 账簿编号,
  #               凭证编号,
  #               凭证日期,
  #               凭证类型编号,
  #               会计主管编号,
  #               substr(凭证日期, 1, 4) as 会计年度,
  #               会计期间序号,
  #               附件张数,
  #               制单人编号,
  #               '"""+timenow+"""' as 更新时间
  #               from temp_pz_main;
  #   """)
  conn.execute("""
    insert into T_API_PZ (ID, ACCDOCCODE, ACCDOCDATE, ACCDOCTYPEID, ACCMANAGERNAME, ACCORGID, ACCPERIODCODE, NUMBEROFATTCH, LEDGER, LEDGERBH, MAKERCODE, OPERATORID, esun_api_update, dwbh, YEAR)
              select
                凭证ID,
                凭证编号,
                凭证日期,
                凭证类型编号,
                会计主管编号,
                '"""+dwid+"""',
                会计期间序号,
                附件张数,
                '"""+zbid+"""',
                '"""+zb+"""',
                制单人编号,
                经办人编号,
                '"""+timenow+"""',
                '"""+dwbh+"""',
                substr(凭证日期, 1, 4)
               from temp_pz_main where 核算账簿编号 = '"""+zb+"""';
    insert into T_API_PZFL (ID, ACCDOCID, accorgid, ABSTRACT, ACCTITLECODE, AMOUNT, LENDINGDIRECTION, esun_api_update, LEDGER, LEDGERBH, DWBH, YEAR)
              select
                凭证分录ID,
                凭证ID,
                '"""+dwid+"""',
                摘要,
                科目编号,
                分录金额,
                case 记账方向
                when '借' then '1'
                when '贷' then '2'
                end,
                '"""+timenow+"""',
                '"""+zbid+"""',
                '"""+zb+"""',
                '"""+dwbh+"""',
                t2.YEAR
               from temp_pz_fl t1 left join T_API_PZ t2 on t1.凭证ID = t2.ID;
    insert into T_API_FZYS (ID, ACCDOCENTRYID, ACCTITLECODE, DEPTBH, RELATEDORGBH, ACCEMPLOYEEBH, WBBH, QUANTITY, UNITPRICE, FOREIGNCURRENCY, EXCHANGERATE, AMOUNT, BIZDATE, BIZCODE, OPERATOR,
              SETTLEMENT, SETTLEMENTNUMBER, APPLICATION, SETTLEMENTDATE, SM01,SM02,SM03,SM04,SM05,SM06,SM07,SM08,SM09,SM10,
              SPECATEBH01,SPECATEBH02,SPECATEBH03,SPECATEBH04,SPECATEBH05,SPECATEBH06,SPECATEBH07,SPECATEBH08,SPECATEBH09,SPECATEBH10,SPECATEBH99,
              BIZDICTCATEBH01,BIZDICTCATEBH02,BIZDICTCATEBH03,BIZDICTCATEBH04,BIZDICTCATEBH05,BIZDICTCATEBH06,BIZDICTCATEBH07,BIZDICTCATEBH08,BIZDICTCATEBH09,BIZDICTCATEBH10,
              esun_api_update, ACCORGID, DWBH, LEDGER, LEDGERBH, YEAR, ACCDOCID)
              select uuid(),t1.凭证分录ID,t1.科目编号,t1.核算部门编号,t1.往来单位编号,t1.核算人员编号,t1.币种编号,t1.数量,t1.单价,t1.外币金额,t1.汇率,t1.金额,t1.业务日期,t1.业务号,t1.经办人,t1.
                结算方式,t1.结算号,t1.用途,t1.结算日期,t1.SM01,t1.SM02,t1.SM03,t1.SM04,t1.SM05,t1.SM06,t1.SM07,t1.SM08,t1.SM09,t1.SM10,t1.
                核算项目01,t1.核算项目02,t1.核算项目03,t1.核算项目04,t1.核算项目05,t1.核算项目06,t1.核算项目07,t1.核算项目08,t1.核算项目09,t1.核算项目10,t1.核算项目99,t1.
                业务字典01,t1.业务字典02,t1.业务字典03,t1.业务字典04,t1.业务字典05,t1.业务字典06,t1.业务字典07,t1.业务字典08,t1.业务字典09,t1.业务字典10,
                '"""+timenow+"""',
                '"""+dwid+"""',
                '"""+dwbh+"""',
                '"""+zbid+"""',
                '"""+zb+"""',
                t2.Year,
                t2.ACCDOCID
               from temp_pz_fzys t1 left join T_API_PZFL t2 on t1.凭证分录ID = t2.ID;
  """)

  conn.execute(f"""
                  delete from T_API_PZFL where ACCDOCID not in (select id from T_API_PZ);
                  delete from T_API_FZYS where ACCDOCID not in (select id from T_API_PZ);
      """)
  conn.close()
  return 200



# run(r'D:\dev\app_td\backend\rpafiles\pzfiles\xxxxxxxxxx20240101-20241231凭证导出.xlsm', timenow)